import { Injectable } from '@nestjs/common'
import { InjectRepository } from '@nestjs/typeorm'
import { Repository, Between, Not, IsNull, Equal } from 'typeorm'
import * as xlsx from 'xlsx'
import { CreateSalaryDto } from './dto/create-salary.dto'
import { UpdateSalaryDto } from './dto/update-salary.dto'
import { Salary } from './entities/salary.entity'
import { Staff } from '../staff/entities/staff.entity'
import { Award } from '../award/entities/award.entity'
import { AwardRuleTypeEnum } from 'src/types/awardRule'
import { Job } from '../job/entities/job.entity'
const moment = require('moment')
@Injectable()
export class SalaryService {
  constructor(
    @InjectRepository(Salary) private readonly salary: Repository<Salary>,
    @InjectRepository(Staff) private readonly staff: Repository<Staff>,
    @InjectRepository(Award) private readonly award: Repository<Award>,
    @InjectRepository(Job) private readonly job: Repository<Job>
  ) {}
  async create(createSalaryDto: CreateSalaryDto) {
    const salary = new Salary()
    const staff = await this.staff.findOne({
      where: { id: createSalaryDto.staffId }
    })
    const job = await this.job.findOne({
      where: { staff: { id: createSalaryDto.staffId } },
      relations: ['staff', 'account']
    })
    const currentDate = new Date()
    const startOfMonth = new Date(
      currentDate.getFullYear(),
      currentDate.getMonth(),
      1
    )
    const endOfMonth = new Date(
      currentDate.getFullYear(),
      currentDate.getMonth() + 1,
      0
    )
    const awards = await this.award.find({
      select: ['awardRule', 'createDate', 'staff'],
      where: {
        staff: { id: createSalaryDto.staffId },
        createDate: Between(startOfMonth, endOfMonth)
      },
      relations: ['staff', 'awardRule']
    })
    let awardSalary: number = 0
    let fineSalary: number = 0
    awards.forEach(award => {
      if (award.awardRule.type === AwardRuleTypeEnum['奖']) {
        awardSalary += award.awardRule.score
      } else {
        fineSalary += award.awardRule.score
      }
    })
    salary.staff = staff
    salary.lunchSalary = job.account.lunchSalary
    salary.trafficSalary = job.account.trafficSalary
    salary.basicSalary = staff.basisSalary
    salary.awardSalary = awardSalary
    salary.fineSalary = fineSalary
    salary.totalSalary =
      Number(staff.basisSalary) +
      Number(awardSalary) -
      Number(fineSalary) +
      Number(job.account.lunchSalary) +
      Number(job.account.trafficSalary)
    salary.remark = `${currentDate.getFullYear()}-${
      currentDate.getMonth() + 1
    }月工资`
    return this.salary.save(salary)
  }

  async findAll(
    params: {
      page: number
      pageSize: number
      isDelete?: boolean
    },
    staffId?: number
  ) {
    const where = {}
    params.isDelete && (where['deleteDate'] = Not(IsNull()))
    staffId && (where['staff'] = { id: staffId })
    const data = await this.salary.find({
      where: { ...where },
      withDeleted: params.isDelete,
      relations: ['staff'],
      skip: (params.page - 1) * params.pageSize,
      take: params.pageSize,
      order: { id: 'DESC' }
    })
    const total = await this.salary.count({
      where: { ...where },
      relations: ['staff'],
      withDeleted: params.isDelete
    })
    const newData = data.map(item => {
      return {
        ...item,
        staff: {
          id: item.staff.id,
          name: item.staff.name
        }
      }
    })
    return { data: newData, total }
  }

  async findOne(id: number) {
    return await this.salary.findOne({ where: { id } })
  }

  async findAvg() {
    // 根据部门获取平均工资
    const data = await this.salary.query(
      `SELECT
        staff.department_id,
        department.name,
        AVG(total_salary) AS avgSalary
      FROM
        salary
        LEFT JOIN staff ON salary.staff_id = staff.id
        LEFT JOIN department ON staff.department_id = department.id
      GROUP BY
        department.name`
    )
    const newData = data.map(item => {
      return {
        department: item.name,
        value: item.avgSalary
      }
    })
    return newData
  }
  async findSum() {
    // 获取总工资
    const data = await this.salary.query(
      `SELECT
          staff.department_id,
          department.name,
          SUM(total_salary) AS sumSalary
        FROM
          salary
          LEFT JOIN staff ON salary.staff_id = staff.id
          LEFT JOIN department ON staff.department_id = department.id
        WHERE salary.remark = '${new Date().getFullYear()}-${new Date().getMonth()}月工资'
        GROUP BY
          department.name`
    )
    const newData = data.map(item => {
      return {
        department: item.name,
        value: item.sumSalary
      }
    })
    return newData
  }
  async findSix() {
    // 获取近6个月的工资按月份分组
    const data = await this.salary.query(
      `SELECT
        DATE_FORMAT(create_date, '%Y-%m') AS month,
        SUM(total_salary) AS value
      FROM
        salary
      Where
        create_date >= CURDATE() - INTERVAL 6 MONTH
      GROUP BY
        month
      ORDER BY
        month ASC`
    )
    const date = []
    for (let i = 6; i >= 0; i--) {
      const time = moment().subtract(i, 'months').format('YYYY-MM')
      date.push(time)
    }
    const newData = []
    date.map(item => {
      const index = data.findIndex(i => i.month === item)
      if (index === -1) {
        newData.push({ month: item, value: 0 })
      } else {
        newData.push(data[index])
      }
    })
    return newData
  }
  findLast() {
    // 获取上月工资详情按部门分组
    return this.salary.query(
      `SELECT
        department.name as department,
        SUM(account.basis_salary) as basis_salary,
        SUM(account.lunch_salary) as lunch_salary,
        SUM(account.traffic_salary) as traffic_salary,
        SUM(salary.award_salary) as award_salary,
        SUM(salary.fine_salary) as fine_salary
      FROM
        salary
        LEFT JOIN staff ON salary.staff_id = staff.id
        LEFT JOIN department ON staff.department_id = department.id
        LEFT JOIN job ON staff.job_id = job.id
        LEFT JOIN account ON job.account_id = account.id
      WHERE
        salary.remark = '${new Date().getFullYear()}-${new Date().getMonth()}月工资'
      GROUP BY
        department.name`
    )
  }

  findYear() {
    // 获取有工资的年份
    return this.salary.query(
      `SELECT
        DATE_FORMAT(create_date, '%Y') AS year
      FROM
        salary
      WHERE
        delete_date IS NULL
      GROUP BY
        year
      ORDER BY
        year ASC`
    )
  }

  findMonth(year: string) {
    // 获取某年的月份
    return this.salary.query(
      `SELECT
        DATE_FORMAT(create_date, '%m') AS month
      FROM
        salary
      WHERE
        DATE_FORMAT(create_date, '%Y') = '${year}'
      AND
        delete_date IS NULL
      GROUP BY
        month
      ORDER BY
        month ASC`
    )
  }
  async findEcharts(
    year?: string,
    month?: string,
    departmentId?: number,
    jobId?: number
  ) {
    /*
      1. 有年份，没有月份
        - x: 月份
      2. 没有年份，没有月份，有部门
        - x: 年份
      3. 有年份，有月份，有部门
        - x: 职位
      4. 有年份，有月份，没有部门
        - x: 部门
    */
    // 获取某年某月某部门某职位的工资详情
    const where = {}
    year && (where['DATE_FORMAT(salary.create_date, "%Y")'] = year)
    month && (where['DATE_FORMAT(salary.create_date, "%m")'] = month)
    departmentId && (where['staff.department_id'] = departmentId)
    jobId && (where['staff.job_id'] = jobId)
    const data = await this.salary.query(
      `SELECT
        ${
          year && !month
            ? 'DATE_FORMAT(salary.create_date, "%m") AS month,'
            : ''
        }
        ${
          !year && !month && departmentId
            ? 'DATE_FORMAT(salary.create_date, "%Y") AS year,'
            : ''
        }
        ${year && month && departmentId ? 'job.name AS job,' : ''}
        ${
          year && month && !departmentId ? 'department.name AS department,' : ''
        }
        SUM(salary.total_salary) AS value
      FROM
        salary
        LEFT JOIN staff ON salary.staff_id = staff.id
        LEFT JOIN department ON staff.department_id = department.id
        LEFT JOIN job ON staff.job_id = job.id
        LEFT JOIN account ON job.account_id = account.id
      ${year || departmentId ? 'WHERE' : ''}
        ${Object.keys(where)
          .map(item => `${item} = ${where[item]}`)
          .join(' AND ')}
      ${year || departmentId ? 'GROUP BY' : ''}
        ${year && !month ? 'month' : ''}
        ${!year && !month && departmentId ? 'year' : ''}
        ${year && month && departmentId ? 'job' : ''}
        ${year && month && !departmentId ? 'department' : ''}
      ${!month ? 'ORDER BY' : ''}
        ${year && !month ? 'month ASC' : ''}
        ${!year && !month && departmentId ? 'year ASC' : ''}
        `
    )
    return data
  }

  // 下载工资表
  async download(year: string, month: string) {
    const data = await this.salary.find({
      where: {
        remark: Equal(`${year}-${month}月工资`)
      },
      relations: ['staff']
    })
    const date = `${year}-${month}`
    // 创建工作簿
    const workbook = xlsx.utils.book_new()
    // 创建工作表
    const worksheet = xlsx.utils.aoa_to_sheet([
      [
        '姓名',
        '基本工资',
        '餐补',
        '交通补助',
        '奖金',
        '罚金',
        '总工资',
        '备注'
      ],
      ...data.map(item => [
        item.staff.name,
        item.staff.basisSalary,
        item.lunchSalary,
        item.trafficSalary,
        item.awardSalary,
        item.fineSalary,
        item.totalSalary,
        item.remark
      ])
    ])
    // 将工作表添加到工作簿
    xlsx.utils.book_append_sheet(workbook, worksheet, date)
    const buffer = xlsx.write(workbook, {
      bookType: 'xlsx',
      type: 'buffer'
    })
    return buffer
  }

  // 获取当月工资是否已经生成
  async findIsCreate() {
    const data = await this.salary.find({
      where: {
        remark: Equal(
          `${new Date().getFullYear()}-${new Date().getMonth() + 1}月工资`
        )
      }
    })
    return data.length > 0
  }

  update(id: number, updateSalaryDto: UpdateSalaryDto) {
    return this.salary.update(id, updateSalaryDto)
  }

  remove(ids: number[]) {
    return this.salary.softDelete(ids)
  }

  restore(ids: number[]) {
    return this.salary.restore(ids)
  }
}
